package com.sjq.project.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.ArrayList;
import java.util.Map;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import com.sjq.project.dao.ProjectDao;
import com.sjq.common.dao.GenericDao;
import com.sjq.common.util.PageResults;
import com.sjq.project.entity.Project;

@Repository
public class ProjectDaoImpl extends GenericDao<Project> implements ProjectDao {

	 @Override
	 public PageResults<?> selectProjectByPage(int pageNo, int pageSize,Project project){
	 	StringBuilder paramSql = new StringBuilder();
		List params = new ArrayList();
		//查询条件
		if (project.getXxzjbh() != null&& !(project.getXxzjbh().equals(""))) {
			paramSql.append(" and xxzjbh=? ");
			params.add(project.getXxzjbh().trim());
		}
		String sql_fenye="select XMDQZT,XXZJBH,XIMU_MC,XIMU_BMJC,XIMU_GJC," +
			"(select zdmc from t_sys_dmx dmx where dmx.zdlbdm='ZDY_XMLB' and  dmx.zddm=xmlbdm) as xmlbdmmc," +
			"(select zdmc from t_sys_dmx dmx where dmx.zdlbdm='ZDY_XMJSYJ' and  dmx.zddm=XMJSYJmc) as XMJSYJmc" +
			"(select org_shortname from t_sys_jgdw_dwxx t where org_code=XMSBDM) as XMSBDWmc,XMSBDW,"
			+	"XMLBDM,XMXZDM,SFSM_PDBZ,XMJSYJDM,XMTZMSDM,XMZJXZ,ZJLYDM,TZKS_JERMBWY,SP_JERMBWY,NDH,to_char(XIMU_KSSJ,'yyyy-MM-dd hh24:mi:ss') as XIMU_KSSJ,to_char(XIMU_JSSJ,'yyyy-MM-dd hh24:mi:ss') as XIMU_JSSJ,XMQYQYSQKDM,QYQJSYSQK_XXQK,JSMB_XXQK,JSNR_XXQK,XMJD_XXQK,JSDW_GAJGMC,JSDW_GAJGJGDM,FZR_YHM,FZR_XM,FZR_LXDH,LXR_YHM,LXR_XM,LXR_LXDH,SHR_XM,SHR_YHM,SHDW_GAJGJGDM,SHR_SPJGDM,SHR_YJ,to_char(SHSJ,'yyyy-MM-dd hh24:mi:ss') as SHSJ,SPR_XM,SPR_YHM,SPDW_GAJGJGDM,SPR_SPJGDM,SPR_YJ,to_char(SPSJ,'yyyy-MM-dd hh24:mi:ss') as SPSJ,DJDW_GAJGJGDM,DJR_YHM,DJR_XM,to_char(DJBGSJ,'yyyy-MM-dd hh24:mi:ss') as DJBGSJ,to_char(DJSJ,'yyyy-MM-dd hh24:mi:ss') as DJSJ,DJBGBS,SJGSDWDM,SJGSRY,BMQS_PDBZ,XMLBXLDM from T_XM_XXB where DJBGBS <>3 " + paramSql.toString() ;
		System.out.println(sql_fenye);
		PageResults<Map<String, String>> results = new PageResults<Map<String, String>>();
		StringBuilder pageSql = new StringBuilder()
				.append("select * from ")
				.append(" (select tmp_1.*, rownum as rowindex from ("+sql_fenye+") tmp_1 ) tmp_2 ")
				.append(" where rowindex between ")
				.append(((pageNo-1)*pageSize + 1))
				.append(" and ")
				.append(((pageNo - 1) * pageSize + pageSize));
		List<Map<String, String>> list=new ArrayList<Map<String, String>>();
		try {
			list = this.getListBySql(pageSql.toString(),params.toArray());
		} catch (SQLException e) {
			
			e.printStackTrace();
		}
		String sql="select count(1) as num from T_XM_XXB where DJBGBS <>3  "+paramSql.toString()+" ";
		SqlRowSet sqlRowSet = this.getJdbcTemplate().queryForRowSet(sql,params.toArray());
		int count = 0;
		while(sqlRowSet.next()){
			count = sqlRowSet.getInt("num");
		}
		results.setTotalCount(count);
        results.setPageNo(pageNo);
        results.setPageSize(pageSize);
		results.setResults(list);
		return results;	
	 }
	 @Override
	 public int addProject(Project project){
	 	String sql="insert into T_XM_XXB ( XMSBDW,xmdqzt,xxzjbh,ximu_mc,ximu_bmjc,ximu_gjc, xmlbdm,xmxzdm,sfsm_pdbz,xmjsyjdm,xmtzmsdm,xmzjxz,zjlydm,tzks_jermbwy,sp_jermbwy,ndh,ximu_kssj,ximu_jssj,xmqyqysqkdm,qyqjsysqk_xxqk,jsmb_xxqk,jsnr_xxqk,xmjd_xxqk,jsdw_gajgmc,jsdw_gajgjgdm,fzr_yhm,fzr_xm,fzr_lxdh,lxr_yhm,lxr_xm,lxr_lxdh,shr_xm,shr_yhm,shdw_gajgjgdm,shr_spjgdm,shr_yj,shsj,spr_xm,spr_yhm,spdw_gajgjgdm,spr_spjgdm,spr_yj,spsj,djdw_gajgjgdm,djr_yhm,djr_xm,djbgsj,djsj,djbgbs,sjgsdwdm,sjgsry,bmqs_pdbz,xmlbxldm) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,to_date(?,'yyyy-MM-dd hh24:mi:ss'),to_date(?,'yyyy-MM-dd hh24:mi:ss'),?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,to_date(?,'yyyy-MM-dd hh24:mi:ss'),?,?,?,?,?,to_date(?,'yyyy-MM-dd hh24:mi:ss'),?,?,?,to_date(?,'yyyy-MM-dd hh24:mi:ss'),to_date(?,'yyyy-MM-dd hh24:mi:ss'),?,?,?,?,?)  ";
	 	Object [] params={project.getXmsbdw(),project.getXmdqzt(),project.getXxzjbh(),project.getXimu_mc(),project.getXimu_bmjc(),project.getXimu_gjc(),project.getXmlbdm(),project.getXmxzdm(),project.getSfsm_pdbz(),project.getXmjsyjdm(),project.getXmtzmsdm(),project.getXmzjxz(),project.getZjlydm(),project.getTzks_jermbwy(),project.getSp_jermbwy(),project.getNdh(),project.getXimu_kssj(),project.getXimu_jssj(),project.getXmqyqysqkdm(),project.getQyqjsysqk_xxqk(),project.getJsmb_xxqk(),project.getJsnr_xxqk(),project.getXmjd_xxqk(),project.getJsdw_gajgmc(),project.getJsdw_gajgjgdm(),project.getFzr_yhm(),project.getFzr_xm(),project.getFzr_lxdh(),project.getLxr_yhm(),project.getLxr_xm(),project.getLxr_lxdh(),project.getShr_xm(),project.getShr_yhm(),project.getShdw_gajgjgdm(),project.getShr_spjgdm(),project.getShr_yj(),project.getShsj(),project.getSpr_xm(),project.getSpr_yhm(),project.getSpdw_gajgjgdm(),project.getSpr_spjgdm(),project.getSpr_yj(),project.getSpsj(),project.getDjdw_gajgjgdm(),project.getDjr_yhm(),project.getDjr_xm(),project.getDjbgsj(),project.getDjsj(),'1',project.getSjgsdwdm(),project.getSjgsry(),project.getBmqs_pdbz(),project.getXmlbxldm()};
	 	return this.getJdbcTemplate().update(sql.toString(),params);
	 }
	 
	 @Override
	 public int deleteProject(String projectId){
		 String sql="update T_XM_XXB set djbgbs=3 where xxzjbh=?";
		 Object [] params={ projectId};
		 return this.getJdbcTemplate().update(sql.toString(),params);
	 }
	 
	  @Override
	 public int updateProject(Project project){
	 	String sql="update T_XM_XXB set XMDQZT=?,XXZJBH=?,XIMU_MC=?,XIMU_BMJC=?,XIMU_GJC=?,XMLBDM=?,XMXZDM=?,SFSM_PDBZ=?,XMJSYJDM=?,XMTZMSDM=?,XMZJXZ=?,ZJLYDM=?,TZKS_JERMBWY=?,SP_JERMBWY=?,NDH=?,XIMU_KSSJ=?,XIMU_JSSJ=?,XMQYQYSQKDM=?,QYQJSYSQK_XXQK=?,JSMB_XXQK=?,JSNR_XXQK=?,XMJD_XXQK=?,JSDW_GAJGMC=?,JSDW_GAJGJGDM=?,FZR_YHM=?,FZR_XM=?,FZR_LXDH=?,LXR_YHM=?,LXR_XM=?,LXR_LXDH=?,SHR_XM=?,SHR_YHM=?,SHDW_GAJGJGDM=?,SHR_SPJGDM=?,SHR_YJ=?,SHSJ=?,SPR_XM=?,SPR_YHM=?,SPDW_GAJGJGDM=?,SPR_SPJGDM=?,SPR_YJ=?,SPSJ=?,DJDW_GAJGJGDM=?,DJR_YHM=?,DJR_XM=?,DJBGSJ=?,DJSJ=?,DJBGBS=?,SJGSDWDM=?,SJGSRY=?,BMQS_PDBZ=?,XMLBXLDM=?  where xxzjbh=?";
	 	Object [] params={project.getXmdqzt(),project.getXxzjbh(),project.getXimu_mc(),project.getXimu_bmjc(),project.getXimu_gjc(),project.getXmlbdm(),project.getXmxzdm(),project.getSfsm_pdbz(),project.getXmjsyjdm(),project.getXmtzmsdm(),project.getXmzjxz(),project.getZjlydm(),project.getTzks_jermbwy(),project.getSp_jermbwy(),project.getNdh(),project.getXimu_kssj(),project.getXimu_jssj(),project.getXmqyqysqkdm(),project.getQyqjsysqk_xxqk(),project.getJsmb_xxqk(),project.getJsnr_xxqk(),project.getXmjd_xxqk(),project.getJsdw_gajgmc(),project.getJsdw_gajgjgdm(),project.getFzr_yhm(),project.getFzr_xm(),project.getFzr_lxdh(),project.getLxr_yhm(),project.getLxr_xm(),project.getLxr_lxdh(),project.getShr_xm(),project.getShr_yhm(),project.getShdw_gajgjgdm(),project.getShr_spjgdm(),project.getShr_yj(),project.getShsj(),project.getSpr_xm(),project.getSpr_yhm(),project.getSpdw_gajgjgdm(),project.getSpr_spjgdm(),project.getSpr_yj(),project.getSpsj(),project.getDjdw_gajgjgdm(),project.getDjr_yhm(),project.getDjr_xm(),project.getDjbgsj(),project.getDjsj(),project.getDjbgbs(),project.getSjgsdwdm(),project.getSjgsry(),project.getBmqs_pdbz(),project.getXmlbxldm()};
	 	return this.getJdbcTemplate().update(sql.toString(),params);
	 }
	 
	 @Override
	 public List<Map<String, String>> findProjectByListAll(Project project){
		StringBuilder paramSql = new StringBuilder();
		List params = new ArrayList();
		//查询条件
		if (project.getXxzjbh() != null&& !(project.getXxzjbh().equals(""))) {
			paramSql.append(" and xxzjbh=? ");
			params.add(project.getXxzjbh().trim());
		}
		String sql="select XMDQZT,XXZJBH,XIMU_MC,XIMU_BMJC,XIMU_GJC,XMLBDM,XMXZDM,SFSM_PDBZ,XMJSYJDM,XMTZMSDM,XMZJXZ,ZJLYDM,TZKS_JERMBWY,SP_JERMBWY,NDH,to_char(XIMU_KSSJ,'yyyy-MM-dd hh24:mi:ss') as XIMU_KSSJ,to_char(XIMU_JSSJ,'yyyy-MM-dd hh24:mi:ss') as XIMU_JSSJ,XMQYQYSQKDM,QYQJSYSQK_XXQK,JSMB_XXQK,JSNR_XXQK,XMJD_XXQK,JSDW_GAJGMC,JSDW_GAJGJGDM,FZR_YHM,FZR_XM,FZR_LXDH,LXR_YHM,LXR_XM,LXR_LXDH,SHR_XM,SHR_YHM,SHDW_GAJGJGDM,SHR_SPJGDM,SHR_YJ,to_char(SHSJ,'yyyy-MM-dd hh24:mi:ss') as SHSJ,SPR_XM,SPR_YHM,SPDW_GAJGJGDM,SPR_SPJGDM,SPR_YJ,to_char(SPSJ,'yyyy-MM-dd hh24:mi:ss') as SPSJ,DJDW_GAJGJGDM,DJR_YHM,DJR_XM,to_char(DJBGSJ,'yyyy-MM-dd hh24:mi:ss') as DJBGSJ,to_char(DJSJ,'yyyy-MM-dd hh24:mi:ss') as DJSJ,DJBGBS,SJGSDWDM,SJGSRY,BMQS_PDBZ,XMLBXLDM from T_XM_XXB where DJBGBS <>3  "+ paramSql.toString() ;
		List<Map<String, String>> list=new ArrayList<Map<String, String>>();
		try {
			list = this.getListBySql(sql.toString(),params.toArray());
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	 
	@Override
	public String buildProjectId() {
		String sql="select 'JS32130000000N'||SEQ_XMID.NEXTVAL as XXZJBH from dual ";
		return (String)this.getJdbcTemplate().queryForMap(sql).get("XXZJBH");
		
	}
	@Override
	public int updateProjectStatus(String projectId, String status) {
		String sql="update T_XM_XXB set xmdqzt=?  where xxzjbh=?";
		 Object [] params={status, projectId};
		 return this.getJdbcTemplate().update(sql.toString(),params);
	}
	@Override
	public Map<String, String> queryProjectById(String projectId) {
		
		String sql="select * from T_XM_XXB where xxzjbh=?";
		 Object [] params={ projectId};
		 try {
			
			 return this.getStringMapBySql(sql, params);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
		
	}
}